# -*- coding: utf-8 -*-
# try something like

import xlrd

from gluon.custom_import import track_changes; track_changes(True)
import import_library_Books_xls

def index(): return dict(message="hello from _dev.py")


def db_reset(): 
    messages = ["Resetting database..."]
    for table in db.tables:
        messages.append("db.{table}.truncate()".format(table=table))
        db[table].truncate()

    return dict(message=PRE("\n".join(messages)))

def db_setup_scaffolding(): 
    messages = ["Setting up database..."]

    ils_insert_admin_groups(db)
    messages.append("ils_insert_admin_groups()")

    ils_insert_ils_item_types(db)
    messages.append("ils_insert_ils_item_types()")

    ils_insert_ils_item_locations(db)
    messages.append("ils_insert_ils_item_locations()")

    ils_insert_ils_item_states(db)
    messages.append("ils_insert_ils_item_states()")

    ils_insert_ils_item_event_types(db)
    messages.append("ils_insert_ils_item_event_types()")

    ils_insert_ils_person_types(db)
    messages.append("ils_insert_ils_person_types()")

    return dict(message=PRE("\n".join(messages)))

def db_setup_test_users():
    messages = ["Setting up test users..."]
    
    auth.add_membership(auth.id_group(role='Head Librarian'),
        db.auth_user.insert(
            username='HeadLibrarian', password=db.auth_user.password.validate('password')[0],
            email='headlibrarian@test.com', first_name='HeadLibrarian', last_name='HeadLibrarian'
        )
    )
    messages.append("Created 'HeadLibrarian'")

    for i in range(1,4):
        db.auth_user.insert(
            username='User{:03d}'.format(i), password=db.auth_user.password.validate('password{:03d}'.format(i))[0],
            email='user{:03d}@test.com'.format(i), first_name='First{:03d}'.format(i), last_name='Last{:03d}'.format(i)
        )
        messages.append("Created 'User{:03d}'".format(i))

    return dict(message=PRE("\n".join(messages)))

def db_setup_test_items():
    messages = ["Setting up test items..."]

    messages.append("Setting up persons...")
    for i in range(1,4):
        db.ils_person.insert(
            full_name='PersonFull{:03d}'.format(i), 
            search_name='PersonSearch{:03d}'.format(i), 
            first_name='PersonFirst{:03d}'.format(i), 
            last_name='PersonLast{:03d}'.format(i), 
        )
        messages.append("Created 'Person{:03d}'".format(i))

    messages.append("Setting up publishers...")
    for i in range(1,4):
        db.ils_publisher.insert(
            name='Publisher{:03d}'.format(i)
        )
        messages.append("Created 'Publisher{:03d}'".format(i))

    return dict(message=PRE("\n".join(messages)))


def import_library_from_xls():
    messages = ["Importing XLS"]
    form = FORM(
        INPUT(_name='xls',_type='file'),
        INPUT(_type='submit')
    )
    data = {}
    if form.process().accepted:
        response.flash = 'form accepted'
        #tmp_str = request.vars.xls.file.read()
        #book = xlrd.open_workbook(request.vars.xls.file.read())
        # encoding_override="cp1252"
        book = xlrd.open_workbook(file_contents=request.vars.xls.file.read(), encoding_override="cp1252")
        messages.append("book={}".format(str(book)))

        import_library_Books_xls.check_headers(book)
        data = import_library_Books_xls.import_library_Books_xls(book)

        # bibliographies
        if (True):
            if (True):
                for t in data['types']:
                    if t == '':
                        pass
                    if not db(db.ils_item_type.name==t).select():
                        db.ils_item_type.insert(name=t)
                        messages.append("inserting type '%s'"%(t))
            if (True):
                for location in data['locations']:
                    if location == '':
                        pass
                    if not db(db.ils_item_location.name==location).select():
                        db.ils_item_location.insert(name=location)
                        messages.append("inserting location '%s'"%(location))
            if (True):
                for publisher in sorted(data['publishers'].keys()):
                    if publisher == '':
                        pass
                    if not db(db.ils_publisher.name==publisher).select():
                        db.ils_publisher.insert(name=publisher)
                        messages.append("inserting publisher '%s'"%(publisher))
    
            if (True):
                for author in data['authors']:
                    if author == '':
                        pass
                    if not db(db.ils_person.full_name==author).select():
                        db.ils_person.insert(full_name=author)
                        messages.append("inserting bibliography_person '%s'"%(author))

            if (True):
                messages.append("processing %d entries"%(len(data['entries'])))
                for i, entry in enumerate(data['entries']):
                    if db(db.ils_item.item_id==entry['number']).select():
                        messages.append("WARNING: entry %s(%d) exists"%(str(entry['title']), entry['number']))
                    else:
                        print "inserting", i, entry
                        e = db.ils_item.insert(
                            item_id = entry['number'],
                            item_title = entry['title'],
                            item_type = db(db.ils_item_type.name==entry['type']).select()[0],
                            item_location = db(db.ils_item_location.name==entry['location']).select()[0],
                            item_publisher = db(db.ils_publisher.name==entry['publisher']).select()[0],
                            item_author = db(db.ils_person.full_name==entry['author']).select()[0]
                        )
                        messages.append("inserting item: %s"%([entry['title'],entry['number']]))

        # circulations
        if (False):
            if (True):
                for member in sorted(data['members'].keys()):
                    if member == '': 
                        pass
                    if ',' in member:
                        member_last, member_first = member.split(',', 2)
                        if not db(db.auth_user.first_name == member_first and db.auth_user.last_name== member_last).select():
                            db.auth_user.insert(first_name=member_first, last_name=member_last)
                            messages.append("auth_user inserting %s, %s"%(member_last, member_first))
                    else:
                        if not db(db.auth_user.username == member).select():
                            db.auth_user.insert(username=member)
                            messages.append("auth_user inserting %s"%(member))
    
            if (True):
                for librarian in sorted(data['librarians'].keys()):
                    if librarian == '':
                        pass
                    if not db(db.auth_user.username == librarian).select():
                        db.auth_user.insert(username=librarian)
                        messages.append("auth_user inserting %s"%(librarian))

    elif form.errors:
        response.flash = 'form has errors'
    return dict(form=form, message=PRE("\n".join(messages)))

